<?php

/**
 * fixtures actions.
 *
 * @package    seth
 * @subpackage fixtures
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class fixturesActions extends sfActions
{
 /**
  * Executes index action
  *
  * @param sfRequest $request A request object
  */
  public function executeIndex(sfWebRequest $request)
  {
    $this->xlsx2yml($request);
  }
  
  protected function xlsx2yml(sfWebRequest $request)
  {
    $fName="sethdbFixturesDevices_06-08-12.xlsx";
    $pieces=explode('.',$fName);
    
    //OPEN xlsx source file
    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objReader->setReadDataOnly(true);
    $PHPWorkbook = $objReader->load(sfConfig::get('app_fixtures_path').$fName );
    
    //CREATE YML destination file
    $fd=fopen(sfConfig::get('app_fixtures_path').$pieces[0].".yml","w");
    
     foreach ($PHPWorkbook->getWorksheetIterator() as $PHPWorksheet){
        //NOME TABELLA
        fwrite($fd,$PHPWorksheet->getTitle().":\n");
        
        
        foreach ($PHPWorksheet->getRowIterator() as $row){	
            if ($row->getRowIndex()>2)
            {
                //ETICHETTA RIGA 1 SPAZIO
                $label=$PHPWorksheet->getCellByColumnAndRow(0, $row->getRowIndex())->getCalculatedValue();
                fwrite($fd," ".$label.":\n");

                //INSERIMENTO COLONNE 2 SPAZI
                $cellIterator = $row->getCellIterator();
                foreach ($cellIterator as $cell) {
                    if (($cell->columnIndexFromString($cell->getColumn())>1) and ($cell->getCalculatedValue()<>""))
                       {
                            //Decide il nome della colonna, controllando se esiste una tabella referenziata da una foreign key (primary key table)
                            $ColumnName=$PHPWorksheet->getCellByColumnAndRow($cell->columnIndexFromString($cell->getColumn())-1,1)->getCalculatedValue()<>""?$PHPWorksheet->getCellByColumnAndRow($cell->columnIndexFromString($cell->getColumn())-1,1)->getCalculatedValue():$PHPWorksheet->getCellByColumnAndRow($cell->columnIndexFromString($cell->getColumn())-1,2)->getCalculatedValue();
                            switch ($ColumnName) 
                            {
                                   case "ip1":
                                   case "ip2":
                                   case "ip3":
                                   case "subnet":
                                   case "gateway":
                                       //CONVERT THE STRING CONTAINING THE IP IN A SIGNED INT (PHP USE ONLY SIGNED INT) AND THEN TO AN UNSIGNED INT FOR MYSQL
                                    $value=sprintf("%u",ip2long($cell->getCalculatedValue()));
                                    break;
                                
                                   case "pac":
                                   case "fac":
                                   case "start":
                                   case "end":
                                       //if (PHPExcel_Shared_Date::isDateTime($cell))
                                        $value=PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "YYYY/MM/DD");
                                       break;
                                   default:
                                       $value=str_replace(array("\r\n", "\n", "\r","\t",":")," ",$cell->getCalculatedValue()); 
                            }
                            fwrite($fd,"  ".$ColumnName.": ".$value."\n");
                    } 
               }
                fwrite($fd,"\n");
            }
        }
        fwrite($fd,"\n");
    }
 
    //CLOSE yml file
    fclose($fd);
  }
  
   
          
}
